package com.spi.soconversion.base.data;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Iterator;

import com.spi.soconversion.base.SOCException;
import com.spi.soconversion.util.ErrorConstants;
import com.spi.soconversion.util.ErrorMessageReader;
import com.spi.soconversion.util.QueryConstants;
import com.spi.soconversion.util.QueryReader;
import com.spi.soconversion.util.ResourceProvider;
import com.spi.soconversion.util.SOCConstants;
import com.spi.soconversion.util.SOCUtility;

/**
 * A class for fetching the data from main source database tables 
 * 
 * @author XSPID41
 *
 */
public class OpenSalesOrderDataCollector {

    static private org.apache.commons.logging.Log log = 
        org.apache.commons.logging.LogFactory.getLog(OpenSalesOrderDataCollector.class);
	
    private Connection connection = null;
	 
	public OpenSalesOrderDataCollector() throws SOCException {
		this.connection = ResourceProvider.getInstance().getdbConnection();
	}
	
	/**
	 * Fetch data from COFI source tables
	 * @throws SOCException
	 */
	public void fetchData() throws SOCException {
		String activeInstallBaseQuery = QueryReader.getQuery(QueryConstants.FETCH_OPEN_ORDERS_QUERY);
        	
    	try  {
    		log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STG_OPEN_ORDER_LINES_STARTS));
    		CallableStatement cs = connection.prepareCall(activeInstallBaseQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result) {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0) {
				log.info(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STG_OPEN_ORDER_LINES)));
				throw new SOCException(
        		ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STG_OPEN_ORDER_LINES));
			}
			
			populateOpenOrdersPOSStoreNumber();
			populateOpenOrdersPOSProvince();
			
			log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STG_OPEN_ORDER_LINES_ENDS + recordsUpdated));
		} catch (SQLException se) {
			log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_STG_OPEN_ORDER_LINES)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_STG_OPEN_ORDER_LINES),se);
		}
	}
	
	public void populateOpenOrdersPOSStoreNumber() throws SOCException
	{
		String closedOrderLinesPOSStoreQuery = QueryReader.getQuery(QueryConstants.SELECT_DISTINCT_POS_STORE_QUERY);
    	
		Statement stmt=null;
    	ResultSet rsInfo=null;
    	ArrayList<String> posStoreNumList = new 	ArrayList<String>();
    	NumberFormat storeFormatter = new DecimalFormat("0000");
    	log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_OPEN_LINES_POS_PROVINCE_POS_STARTS));
    	try
		{
    		stmt=connection.createStatement();
    		rsInfo=stmt.executeQuery(closedOrderLinesPOSStoreQuery);
			if(rsInfo!=null)
			{
				while(rsInfo.next())
				{
					String storeNum = rsInfo.getString("POSSTORENO");
					posStoreNumList.add(storeFormatter.format(Integer.parseInt(storeNum))); 
				}
				
			}			
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
			log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_FETCH_POS_STORE_NUM)+SOCConstants.NEW_LINE+sqle.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_FETCH_POS_STORE_NUM) ,sqle);
    		
		}	
		
		String closedOrderLinesPOSStoreFormattedQuery = QueryReader.getQuery(QueryConstants.INSERT_DISTINCT_POS_STORE_QUERY);
		
		PreparedStatement posStoreNumStmt = null;
		try {
			connection.setAutoCommit(false);
			posStoreNumStmt = connection.prepareStatement(closedOrderLinesPOSStoreFormattedQuery);
		} catch (SQLException se) {
			log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
					ErrorConstants.FAILED_CREATE_STMT_TBL_OPEN_LINES_POS_PROVINCE)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
					ErrorMessageReader.getError(
							ErrorConstants.FAILED_CREATE_STMT_TBL_OPEN_LINES_POS_PROVINCE),se);
		}	   
	   
		Iterator<String> iter=posStoreNumList.iterator();
		while(iter.hasNext())
    	{	
			String  posStoreNum = iter.next();
			try {
				posStoreNumStmt.setString(1,posStoreNum);
				posStoreNumStmt.addBatch();
			} catch (SQLException se) {
				log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
						ErrorConstants.FAILED_ADDING_BATCH_TBL_OPEN_LINES_POS_PROVINCE)+SOCConstants.NEW_LINE+se.getMessage()));
				throw new SOCException(
						ErrorMessageReader.getError(
								ErrorConstants.FAILED_ADDING_BATCH_TBL_OPEN_LINES_POS_PROVINCE),se);
			}
			
    	}
		
		   try {
				posStoreNumStmt.executeBatch();
				connection.commit();
				connection.setAutoCommit(true);
				log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_OPEN_LINES_POS_PROVINCE_POS_ENDS));
			} catch (SQLException se) {
				log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
						ErrorConstants.FAILED_UPDATE_BATCH_TBL_OPEN_LINES_POS_PROVINCE)+SOCConstants.NEW_LINE+se.getMessage()));
				throw new SOCException(
						ErrorMessageReader.getError(
								ErrorConstants.FAILED_UPDATE_BATCH_TBL_OPEN_LINES_POS_PROVINCE),se);
			}
		
	}
	
	public void populateOpenOrdersPOSProvince() throws SOCException
	{
		String closedOrderLinesPOSProvinceQuery = QueryReader.getQuery(QueryConstants.INSERT_POS_PROVINCE_QUERY);
    	
		Statement stmt=null;
	 	log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_OPEN_LINES_POS_PROVINCE_POS_PROVINCE_STARTS));
		try
		{
    		stmt=connection.createStatement();
    		stmt.executeUpdate(closedOrderLinesPOSProvinceQuery);		
		}
		catch(SQLException sqle)
		{
			log.error(SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_UPDATE_TBL_OPEN_LINES_POS_PROVINCE)+SOCConstants.NEW_LINE+sqle.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_UPDATE_TBL_OPEN_LINES_POS_PROVINCE) ,sqle);
    		
		}	
	 	log.info(SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_OPEN_LINES_POS_PROVINCE_POS_PROVINCE_ENDS));
	}
}